package org.dusin.util;

import java.io.FileInputStream;
import java.io.IOException;

import java.util.List;

import java.io.OutputStream;

import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.util.Date;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelUtil {

    public ExcelUtil() {
    }

    private static HSSFWorkbook readFile(String filename) throws IOException {
        return new HSSFWorkbook(new FileInputStream(filename));
    }

    /**
     * @param list,写入文件的数据
     * @param fields，写入的字段
     * @param out
     * @param filename，载入的模板文件
     * @param startRow，起始行号
     * @param startCol，起始列号
     * @throws IOException
     */
    public void getExcelFileFromTemplate(List<Map<String, Object>> list, String[] fields, OutputStream out, String filename, int startRow, int startCol) throws IOException {
        Workbook wb = readFile(filename);
        Sheet sheet1 = wb.getSheetAt(0);
        CreationHelper createHelper = wb.getCreationHelper();
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setDataFormat(
                createHelper.createDataFormat().getFormat("0.000"));

        CellStyle cellStyleDate = wb.createCellStyle();     //日期格式
        cellStyleDate.setDataFormat(
                createHelper.createDataFormat().getFormat("yy-m-d h:mm:ss"));

        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> map = list.get(i);            
            Row row = sheet1.createRow(startRow + i);
            for (int j = 0; j < fields.length; j++) {
                Object value = map.get(fields[j]);
                Cell cell = row.createCell(startCol + j);                
                if (value != null) {
                    if (value instanceof Integer) {
                        cell.setCellValue((Integer) value);
                    } else if (value instanceof Double) {
                        cell.setCellValue((Double) value);
                        cell.setCellStyle(cellStyle);
                    } else if (value instanceof Date) {
                        cell.setCellValue((Date) value);
                    } else if (value instanceof BigDecimal) {
                        cell.setCellValue(((BigDecimal) value).doubleValue());
                    } else {
                        cell.setCellValue(value.toString());
                    }
                } else {
                    cell.setCellValue("");
                }
            }
        }
        wb.write(out);
    }

    /**
     * @param title,标题
     * @param list,写入文件的数据
     * @param fields，写入的字段
     * @param out
     * @param filename，载入的模板文件
     * @param startRow，起始行号
     * @param startCol，起始列号
     * @throws IOException
     */
    public void getExcelFileFromTemplate(String title, List<Map<String, Object>> list, String[] fields, OutputStream out, String filename, int startRow, int startCol) throws IOException {
        Workbook wb = readFile(filename);
        Sheet sheet1 = wb.getSheetAt(0);
        CreationHelper createHelper = wb.getCreationHelper();
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setDataFormat(
                createHelper.createDataFormat().getFormat("0.000"));

        CellStyle cellStyleDate = wb.createCellStyle();     //日期格式
        cellStyleDate.setDataFormat(
                createHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));

        if (title != null) {
            Cell titleCell = sheet1.getRow(0).getCell(0);
            //Cell titleCell = sheet1.createRow(0).createCell(0);
            titleCell.setCellValue(title);
        }

        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> map = list.get(i);
            //Row row = sheet1.createRow(startRow+i);
            Row row = sheet1.createRow(startRow + i);
            for (int j = 0; j < fields.length; j++) {

                Object value = map.get(fields[j]);
                Cell cell = row.createCell(startCol + j);
                if ("rate".equals(fields[j]) || "upperLimitRate".equals(fields[j]) || "lowerLimitRate".equals(fields[j])) {
                    cell.setCellStyle(cellStyle);
                }/*else if("minVoltageTime".equals(fields[j])||"minVoltageTime".equals(fields[j])){
                 cell.setCellStyle(cellStyleDate);
                 }*/

                if (value != null) {
                    if (value instanceof Integer) {
                        cell.setCellValue((Integer) value);
                    } else if (value instanceof Double) {
                        cell.setCellValue((Double) value);
                    } else if (value instanceof Date) {
                        cell.setCellValue((Date) value);
                        cell.setCellStyle(cellStyleDate);
                    } else if (value instanceof BigDecimal) {
                        cell.setCellValue(((BigDecimal) value).doubleValue());
                    } else {
                        cell.setCellValue(value.toString());
                    }
                } else {
                    cell.setCellValue("");
                }
            }
        }
        wb.write(out);
    }

}
